/**  定义Driverschool相关的接口 */
const express = require("express");
const router = express.Router();
const Response = require("../utils/Response.js");
// 引入mysql连接池
const pool = require("../utils/db.js");
// 新增驾校
router.post("/driverschool/add", (req, resp) => {
  let obj = req.body;
  let sql = "insert into school set ?";
  pool.query(sql, [obj], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    // 注册成功
    resp.send(Response.ok());
  });
});

// 查询驾校标签
router.get("/driverschool/types", (req, resp) => {
  let sql = "select * from school_label";
  pool.query(sql, (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    // 查询成功 返回标签
    resp.send(Response.ok({ result }));
  });
});

// 查询驾照类型
router.get("/driverschool/license", (req, resp) => {
  let sql = "select * from license";
  pool.query(sql, (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    // 查询成功 返回驾照类型
    resp.send(Response.ok({ result }));
  });
});

// 通过驾照类型查询数据 
router.get("/driverschool/ByType", async (req, resp) => {
  let { page, pagesize, type,user_lng, user_lat, } = req.query;
  let startIndex = (page - 1) * pagesize;
  let size = parseInt(pagesize);
  try {
    let sql = `select *, (6371 * ACOS(COS( radians( ? )) * cos( radians( school_lat ) ) * cos( radians( school_lng ) - radians( ? ) ) + sin(radians( ? )) * sin(radians( school_lat )))) AS distance FROM school HAVING licensetype like '%${type}%' limit ?,?`;
    let result = await pool.querySync(sql, [user_lat,
      user_lng,
      user_lat,startIndex, size]);
    let sql2 = `select count(*) as total from school where licensetype like '%${type}%'`;
    let result2 = await pool.querySync(sql2);
    resp.send(
      Response.ok({
        result,
        total: result2[0].total,
        pageIndex: page,
        pageSize: pagesize,
      })
    );
  } catch (error) {
    resp.send(Response.error(error));
  }
});

// 通过驾照标签查询数据 
router.get("/driverschool/ByLabel", async (req, resp) => {
  let { page, pagesize, label,user_lng, user_lat, } = req.query;
  let startIndex = (page - 1) * pagesize;
  let size = parseInt(pagesize);
  try {
    let sql = `select *, (6371 * ACOS(COS( radians( ? )) * cos( radians( school_lat ) ) * cos( radians( school_lng ) - radians( ? ) ) + sin(radians( ? )) * sin(radians( school_lat )))) AS distance FROM school HAVING schoollabel like '%${label}%' limit ?,?`;
    let result = await pool.querySync(sql, [user_lat,
      user_lng,
      user_lat,startIndex, size]);
    let sql2 = `select count(*) as total from school where schoollabel like '%${label}%'`;
    let result2 = await pool.querySync(sql2);
    resp.send(
      Response.ok({
        result,
        total: result2[0].total,
        pageIndex: page,
        pageSize: pagesize,
      })
    );
  } catch (error) {
    resp.send(Response.error(error));
  }
});

// 查询驾校列表 分页
router.get("/driverschool/list", async (req, resp) => {
  let { page, pagesize } = req.query;
  let startIndex = (page - 1) * pagesize;
  let size = parseInt(pagesize);
  try {
    let sql = "select * from school limit ?,?";
    let result = await pool.querySync(sql, [startIndex, size]);
    let sql2 = "select count(*) as total from school";
    let result2 = await pool.querySync(sql2);
    resp.send(
      Response.ok({
        result,
        total: result2[0].total,
        pageIndex: page,
        pageSize: pagesize,
      })
    );
  } catch (error) {
    resp.send(Response.error(error));
  }
});

// 模糊查询驾校列表
router.get("/driverschool/ByName", async (req, resp) => {
  let { page, pagesize, schoolName,user_lng, user_lat } = req.query;
  let startIndex = (page - 1) * pagesize;
  let size = parseInt(pagesize);
  try {
    let sql = `select *, (6371 * ACOS(COS( radians( ? )) * cos( radians( school_lat ) ) * cos( radians( school_lng ) - radians( ? ) ) + sin(radians( ? )) * sin(radians( school_lat )))) AS distance FROM school HAVING school_name like '%${schoolName}%' limit ?,?`;
    let result = await pool.querySync(sql, [user_lat,
      user_lng,
      user_lat,startIndex, size]);
    let sql2 = `select count(*) as total from school where school_name like '%${schoolName}%'`;
    let result2 = await pool.querySync(sql2);
    resp.send(
      Response.ok({
        result,
        total: result2[0].total,
        pageIndex: page,
        pageSize: pagesize,
      })
    );
  } catch (error) {
    resp.send(Response.error(error));
  }
});
// 查询距离用户最近的驾校列表
router.get("/driverschool/distance", async (req, resp) => {
  let { page, pagesize, user_lng, user_lat } = req.query;
  let startIndex = (page - 1) * pagesize;
  let size = parseInt(pagesize);
  try {
    let sql = `SELECT *,(6371 * ACOS(COS( radians( ? )) * cos( radians( school_lat ) ) * cos( radians( school_lng ) - radians( ? ) ) + sin(radians( ? )) * sin(radians( school_lat )))) AS distance FROM school  ORDER BY distance ASC LIMIT ?,?`;
    let result = await pool.querySync(sql, [
      user_lat,
      user_lng,
      user_lat,
      startIndex,
      size,
    ]);
    let sql2 = `SELECT count(*) total FROM (
      SELECT *,(6371 * ACOS(COS( radians( ? )) * cos( radians( school_lat ) ) * cos( radians( school_lng ) - radians( ? ) ) + sin(radians( ? )) * sin(radians( school_lat )))) AS distance FROM school) subq`;
    let result2 = await pool.querySync(sql2,[
      user_lat,
      user_lng,
      user_lat,
    ]);
    resp.send(
      Response.ok({
        result,
        total: result2[0].total,
        pageIndex: page,
        pageSize: pagesize,
      })
    );
  } catch (error) {
    resp.send(Response.error(error));
  }
});

// 查询距离用户最近范围内的驾校列表
router.get("/driverschool/ByGap", async (req, resp) => {
  let { page, pagesize, user_lng, user_lat, distance } = req.query;
  let startIndex = (page - 1) * pagesize;
  let size = parseInt(pagesize);
  try {
    let sql = `SELECT *,(6371 * ACOS(COS( radians( ? )) * cos( radians( school_lat ) ) * cos( radians( school_lng ) - radians( ? ) ) + sin(radians( ? )) * sin(radians( school_lat )))) AS distance FROM school HAVING distance < ? ORDER BY distance ASC LIMIT ?,?`;
    let result = await pool.querySync(sql, [
      user_lat,
      user_lng,
      user_lat,
      distance,
      startIndex,
      size,
    ]);
    let sql2 = `SELECT count(*) total FROM (
      SELECT *,(6371 * ACOS(COS( radians( ? )) * cos( radians( school_lat ) ) * cos( radians( school_lng ) - radians( ? ) ) + sin(radians( ? )) * sin(radians( school_lat )))) AS distance FROM school) subq WHERE distance < ?`;
    let result2 = await pool.querySync(sql2,[
      user_lat,
      user_lng,
      user_lat,
      distance
    ]);
    resp.send(
      Response.ok({
        result,
        total: result2[0].total,
        pageIndex: page,
        pageSize: pagesize,
      })
    );
  } catch (error) {
    resp.send(Response.error(error));
  }
});

// 驾校评分排列
router.get("/driverschool/score", async (req, resp) => {
  let { page, pagesize,user_lng, user_lat } = req.query;
  let startIndex = (page - 1) * pagesize;
  let size = parseInt(pagesize);
  try {
    let sql = `SELECT *,(6371 * ACOS(COS( radians( ? )) * cos( radians( school_lat ) ) * cos( radians( school_lng ) - radians( ? ) ) + sin(radians( ? )) * sin(radians( school_lat )))) AS distance FROM school ORDER BY school_score DESC LIMIT ?,?`;
    let result = await pool.querySync(sql, [
      user_lat,
      user_lng,
      user_lat,
      startIndex,
      size
      
    ]);
    let sql2 = `SELECT count(*) as total FROM school`;
    let result2 = await pool.querySync(sql2,[]);
    resp.send(
      Response.ok({
        result,
        total: result2[0].total,
        pageIndex: page,
        pageSize: pagesize,
      })
    );
  } catch (error) {
    resp.send(Response.error(error));
  }
});

// 通过id查询驾校信息
router.get("/driverschool/ById", async (req, resp) => {
  let { school_id} = req.query;
  let sql = "SELECT * from school where school_id=?";
  pool.query(sql,[school_id], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    // 查询成功 返回驾照类型
    resp.send(Response.ok({ result }));
  });
});

// 删除驾校
router.get("/driverschool/del", async (req, resp) => {
  let { school_id} = req.query;
  let sql = "delete from school where school_id=?";
  pool.query(sql,[school_id], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    if (result.affectedRows === 0) {
      resp.send(Response.error(400, '删除失败'))
    } else {
      // 删除成功
      resp.send(Response.ok());
    }
  });
});

// 修改驾校信息
router.post("/driverschool/update", (req, resp) => {
  let obj = req.body;
  let sql = "update school set ? where school_id=?";
  pool.query(sql, [obj, obj.school_id], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    // 修改成功
    resp.send(Response.ok());
  });
});
// 将router对象导出
module.exports = router;
